delete from wli_qs_hospital where msg_guid = 1;
delete from wli_qs_report_data where msg_guid = 1;
delete from wli_qs_report_attribute where msg_guid = 1;
commit;

insert
into wli_qs_report_attribute
  (
    msg_guid,
    db_timestamp,
    localhost_timestamp,
    host_name,
    state,
    inbound_service_name,
    inbound_service_uri,
    inbound_operation,
    outbound_service_name,
    outbound_service_uri,
    outbound_operation,
    msg_labels,
    error_code,
    error_reason,
    error_details
  )
  values
  (
    '1',
    sysdate,
    sysdate,
    'node1.vijfhuizen.local',
    '1',
    'inbound_service_name',
    'inbound_service_uri',
    'inbound_operation',
    'outbound_service_name',
    'outbound_service_uri',
    'outbound_operation',
    'msg_labels',
    'error_code',
    'error_reason',
    'error_details'
  );


declare
  v_clob clob := xmltype('<Warehouse whNo="100"><Building>Owned</Building></Warehouse>').getclobval();
  pos pls_integer := 1;
  buffer raw( 32767 );
  res blob;
  lob_len pls_integer := dbms_lob.getlength(v_clob);
BEGIN
  dbms_lob.createtemporary( res, true );
  dbms_lob.open( res, dbms_lob.lob_readwrite );
  loop
    buffer := utl_raw.cast_to_raw( dbms_lob.substr( v_clob, 16000, pos ) );
    if utl_raw.length( buffer ) > 0 then
      dbms_lob.writeappend( res, utl_raw.length( buffer ), buffer );
    end if;
    pos := pos + 16000;
    exit when pos > lob_len;
  end loop;

  insert
 into wli_qs_report_data
  (
    msg_guid,
    data_type,
    encoding,
    data_value
  )
  values
  (
    '1',
    '1',
    '1',
    res
  );
end;
/